***********************************************************************************************
************					TBL 1 - Trip-Level Summary Statistics by Decile of Travel *****
************					Time Savings
************************************************************************************************/
local fn .\data\clean\I10W_laneuse_dataset_15nov14_wcensus

use "`fn'", clear
drop if holiday==1 | dow==0 | dow==6
merge m:1 acct_no using .\data\clean\ZIP_accts, keep(1 3) nogen         // Confidential account ZIP codes
sort zip_code
merge m:1 zip_code using .\data\clean\censusdata, keep(1 3) nogen
sort acct_no
merge m:1 acct_no using .\data\clean\vehicles_used, keep(1 3) nogen          // Confidential account vehicle make, model type

gen TT_dif_hr=dist/MLspeed-dist/ELspeed
gen WTP2=charged_toll/TT_dif_hr
drop if ELspeed==.
drop if TT_dif_hr==.
la var TT_dif_hr "Time in Hours"
drop if dow==0|dow==6
keep if acct_type=="PRIVATE"&occupancy~="HOV-3"
keep if hour>4 & hour<9
drop if TT_dif_hr<0
drop if holiday==1
g decile=.
g TT_dif_min=TT_dif_hr*60

forval i= 10(10)90{
	qui centile TT_dif_hr, c(`i')
	replace decile=`i'/100 if TT_dif_hr<=`r(c_1)' & decile==.
	}
replace decile=1 if decile==.
tabstat TT_dif_hr, by(decile)
replace decile=round(decile*10)
g count=1
egen freqpermo=sum(count), by(acct_no month)
egen firstmonth=min(month), by(acct_no)
replace freqpermo=. if month==firstmonth
g hrlywage=mean_inc/(2040*2)

//PANEL A

matrix define A=J(11,6,.)
forval d=1/10{
	qui sum TT_dif_hr if decile==`d'
	local d1_`d'=round(`r(mean)',.01)
	qui sum TT_dif_min if decile==`d'
	local d2_`d'=round(`r(mean)',.01)
	qui sum ELspeed if decile==`d'
	local d3_`d'=round(`r(mean)',.1)
	qui sum MLspeed if decile==`d'
	local d4_`d'=round(`r(mean)',.1)
	qui sum dist if decile==`d'
	local d5_`d'=round(`r(mean)',.1)
	matrix A[`d',1]=`d'
	matrix A[`d',2]=`d1_`d''
	matrix A[`d',3]=`d2_`d''
	matrix A[`d',4]=`d3_`d''
	matrix A[`d',5]=`d4_`d''
	matrix A[`d',6]=`d5_`d''
	}
local i=2
foreach v in TT_dif_hr TT_dif_min ELspeed MLspeed dist{
	qui sum `v'
	local l_`i'=round(`r(mean)',.01)
	matrix A[11,`i']=`l_`i''
	local i=`i'+1
	}

matrix colnames A = Decile TimesavingsHrs TimesavingsMins ELSpeed MLSpeed Dist
matrix list A

//PANEL B

preserve
	use "`fn'", clear
	set more off
	drop if holiday==1
	drop if dow==6 | dow==0
	
	// Construct usage frequency
	keep if inlist(hour,5,6,7,8) /*period=="AM Peak"*/
	keep if acct_type=="PRIVATE"
	drop TTdiff_ELSpeed
	g TTdiff_ELSpeed= (dist/MLspeed)-(dist/ELspeed)
	g negTTdiff=cond(TTdiff_ELSpeed<=0,1,cond(TTdiff_ELSpeed==.,.,0))
	tab negTTdiff
	drop if TTdiff_ELSpeed<=0
	gen pay_trip=charged_toll>0&charged_toll~=.
	gen hov_trip=charged_toll==0
	gen trip=charged_toll~=.

	collapse (sum) pay_trip hov_trip trip, by(acct_no month)
	drop if acct_no==""
	sort acct_no month
	by acct_no: gen temp=_n
	drop if temp==1
	collapse (mean) pay_trip hov_trip trip, by(acct_no)

	gen pay_bin=0
	replace pay_bin=1 if 1>=pay_trip&pay_trip>0
	replace pay_bin=2 if 5>=pay_trip&pay_trip>1
	replace pay_bin=3 if 10>=pay_trip&pay_trip>5
	replace pay_bin=4 if 15>=pay_trip&pay_trip>10
	replace pay_bin=5 if 20>=pay_trip&pay_trip>15
	replace pay_bin=6 if 10000>=pay_trip&pay_trip>20
	sort acct_no
	save .\data\temp\acct_category, replace

	use "`fn'", clear
	keep if acct_type=="PRIVATE"
	drop TTdiff_ELSpeed
	drop if holiday==1
	drop if dow==6 | dow==0
	g TTdiff_ELSpeed= (dist/MLspeed)-(dist/ELspeed)
	keep if inlist(hour,5,6,7,8) /*period=="AM Peak"*/
	gen WTP=charged_toll/(TTdiff_ELSpeed)
	gen temp1=WTP<0

	*Tabulate usage by decile
	sort acct_no
	merge m:1 acct_no using .\data\temp\acct_category
	keep if WTP>0&WTP~=.
	xtile decile = TTdiff_ELSpeed if WTP>0&WTP~=., nq(10)
	g TTdiff_ELSpeed_min=TTdiff_ELSpeed*60
	tabstat  pay_trip, by(decile) save 
	matrix define B=J(11,1,.)
	forval d=1/10{
		matrix B[`d',1]=r(Stat`d')
		}
	matrix B[11,1]=r(StatTotal)
restore
* Frequency wages, toll by decile
matrix define C=J(11,4,.)
forval d=1/10{
	qui sum freqpermo if decile==`d'
	local d1_`d'=round(`r(mean)',.01)
	qui sum hrlywage if decile==`d'
	local d2_`d'=round(`r(mean)',.01)
	qui sum charged_toll if decile==`d'
	local d3_`d'=round(`r(mean)',.01)
	matrix C[`d',1]=`d'
	matrix C[`d',2]=`d1_`d''
	matrix C[`d',3]=`d2_`d''
	matrix C[`d',4]=`d3_`d''
	}
local i=2
foreach v in freqpermo hrlywage charged_toll{
	qui sum `v'
	local l_`i'=round(`r(mean)',.01)
	matrix C[11,`i']=`l_`i''
	local i=`i'+1
	}
matrix colnames C = Decile AvgUsesperMo AvgWage AvgToll
matrix list C
*/
** Modal vehicle type and Average Vehicle Value by Decile

use "`fn'", clear
drop       perc_white    perc_asian    mean_inc      percmale      percunder25   perc35_50 ///
		zip           perc_black    popul         perc_hispo~n  percfemale    perc25_35     percover50
*create frequency
keep if inlist(hour,5,6,7,8) /*period=="AM Peak"*/
drop if holiday==1
drop if dow==6 | dow==0
keep if acct_type=="PRIVATE"
drop if TTdiff_ELSpeed<=0
gen pay_trip=charged_toll>0&charged_toll~=.
gen hov_trip=charged_toll==0
gen trip=charged_toll~=.

collapse (sum) pay_trip hov_trip trip, by(acct_no month)

drop if acct_no==""
sort acct_no month
by acct_no: gen temp=_n
drop if temp==1
collapse (mean) pay_trip hov_trip trip, by(acct_no)

gen pay_bin=0
replace pay_bin=1 if 1>=pay_trip&pay_trip>0
replace pay_bin=2 if 5>=pay_trip&pay_trip>1
replace pay_bin=3 if 10>=pay_trip&pay_trip>5
replace pay_bin=4 if 15>=pay_trip&pay_trip>10
replace pay_bin=5 if 20>=pay_trip&pay_trip>15
replace pay_bin=6 if 10000>=pay_trip&pay_trip>20
sort acct_no
save .\data\temp\acct_category_v2, replace

use "`fn'", clear
drop       perc_white    perc_asian    mean_inc      percmale      percunder25   perc35_50 ///
		zip           perc_black    popul         perc_hispo~n  percfemale    perc25_35     percover50
keep if acct_type=="PRIVATE"
sort acct_no
merge m:1 acct_no using .\data\clean\ZIP_accts, keep(1 3) nogen
sort zip_code
merge m:1 zip_code using .\data\clean\censusdata, keep(1 3) nogen
sort acct_no
merge m:1 acct_no using .\data\clean\vehicles_used, keep(1 3) nogen

	drop if holiday==1
	drop if dow==6 | dow==0
keep if inlist(hour,5,6,7,8) /*period=="AM Peak"*/
gen WTP=charged_toll/(TTdiff_ELSpeed/60)
gen temp1=WTP<0
gen wage=mean_inc/2/2040
gen Price_per_mile=charged_toll/dist
g miles=cond(dist<2,"0-2", ///
	cond(dist<5, "2-5", ///
	cond(dist<7, "5-7", "7-10.5")))
	
*create frequency
sort acct_no
merge m:1 acct_no using .\data\temp\acct_category
keep if WTP>0&WTP~=.
xtile decile = TTdiff_ELSpeed if WTP>0&WTP~=., nq(10)

g count=1
egen decile_count=sum(count), by(decile)
g decile_shr=1/decile_count

g space=" - "
replace vehicle_make=strproper(vehicle_make)
replace vehicle_model=strproper(vehicle_model)
egen make_model=concat(vehicle_make space vehicle_model)
set more off


preserve
capture drop count
g count=1
collapse (sum) count, by(make_model decile)
gsort decile -count 
egen num=seq(), by(decile)
drop if num>1
duplicates drop make_model decile, force
keep num make_model decile
reshape wide make_model, i(decile) j(num)
tempfile temp 
save `temp'
restore, preserve
capture drop count
g count=1
collapse (sum) count, by(make_model)
gsort -count 
egen num=seq()
drop if num>1
duplicates drop make_model, force
keep make_model num
g decile=11
reshape wide make_model, i(decile) j(num)
append using `temp'
sort decile
tempfile temp1
save `temp1'
restore

	
	
*****************************************************************************************
***								MSRP Column											*****
*****************************************************************************************

/* 2009 NHTS data never used, just for validation
use ".\data\clean\2009full.dta" , clear
keep if hhstate=="CA"
cpigen
qui sum cpiu if year==2009
local cpi13=`r(mean)'
qui sum cpiu if year==2010
local cpi10=`r(mean)'
replace msrp=msrp*(`cpi13'/`cpi10')
drop if msrp==.
g msrp_depr=msrp*(.8^min(vehage,6)*.95^max(0,vehage-6))
g exp_sh=msrp_depr/impinc
qui sum exp_sh if exp_sh<1
local exp_sh=`r(mean)'
di `exp_sh'
*/
use ".\data\clean\acct_reg_attributes_vehicles_msrp_070615.dta", clear
drop if msrp==.
cpigen
qui sum cpiu if year==2013
local cpi13=`r(mean)'
qui sum cpiu if year==2010
local cpi10=`r(mean)'
replace msrp=msrp*(`cpi10'/`cpi13')
g age=2013-min(year,2013)+1
g msrp_depr20_5=msrp*(.8^min(age,6)*.95^max(0,age-6))
label var VOT "Value of Time ($/hr.)"
label var urgency "Urgency ($)"
*g exp_inc_nhts=msrp_/(`exp_sh'*8*260*2)
*g exp_inc_cex=msrp_/(.3947171*8*260*2)
label var msrp_ "Vehicle Price in 2013 Dollars (MSRP)"
*label var exp_inc_nhts "1/2 Implied Hourly Wage (CEX, $)"
*label var exp_inc_nhts "1/2 Implied Hourly Wage (NHTS, $)"

keep acct_no msrp_depr20_5 year
collapse msrp_depr20_5 year, by(acct_no)
ren year veh_year
tempfile temp
save `temp'

use "`fn'", clear
gen TT_dif_hr=dist/MLspeed-dist/ELspeed
gen WTP2=charged_toll/TT_dif_hr
drop if ELspeed==.
drop if TT_dif_hr==.
la var TT_dif_hr "Time in Hours"
drop if dow==0|dow==6
drop if holiday==1
keep if acct_type=="PRIVATE"&occupancy~="HOV-3"
keep if hour>4 & hour<9
drop if TT_dif_hr<0
g decile=.
g TT_dif_min=TT_dif_hr*60

forval i= 10(10)90{
	qui centile TT_dif_hr, c(`i')
	replace decile=`i'/100 if TT_dif_hr<=`r(c_1)' & decile==.
	}
replace decile=1 if decile==.
tabstat TT_dif_hr, by(decile)
replace decile=round(decile*10)
merge m:1 acct_no using `temp'
matrix define D=J(11,1,.)
tabstat msrp_, by(decile) s(mean) save
forval i=1/10{
	matrix D[`i',1]=r(Stat`i')
	}
matrix D[11,1]=r(StatTotal)
putexcel set ".\results\maintablesfigs\maintextFigsTabs.xlsx", modify sheet("Table 1")
putexcel B2=matrix(A)
putexcel B14=matrix(C)
putexcel C14=matrix(B)
putexcel G14=matrix(D)
putexcel B1 = "Decile of Travel Time Savings"
putexcel C1 = "Time Savings in Hours"
putexcel D1 = "Time Savings in Min"
putexcel E1 = "Avg. EL/HV Speed in MPH"
putexcel F1 = "Avg. ML Speed in MPH"
putexcel G1 = "Avg. Dist. Traveled in Miles"
putexcel B13 = "Decile of Travel Time Savings"
putexcel C13 = "Avg. Uses per Month"
putexcel D13 = "Avg. Hourly Wage in Zip Code"
putexcel E13 = "Avg. Toll Paid"
putexcel F13 = "Model Vehicle Registered to Account"
putexcel G13 = "Avg. Vehicle Value"
preserve
use `temp1', clear
keep make_model
export excel make_model using ".\results\maintablesfigs\maintextFigsTabs.xlsx", ///
	sheet("Table 1") sheetmodify cell(F14)
restore
